package net.hn.hnms.biz.sewage.service;

import lombok.RequiredArgsConstructor;
import net.hn.hnms.sys.common.core.domain.ResponsePage;
import net.hn.hnms.sys.common.core.utils.StringUtils;
import net.hn.hnms.sys.common.mybatis.core.page.PageQuery;
import net.hn.hnms.sys.common.satoken.utils.LoginHelper;
import net.hn.hnms.sys.system.api.model.LoginUser;
import net.hn.hnms.biz.sewage.dao.*;
import net.hn.hnms.biz.sewage.entity.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @author 智呈物联 nzw
 * @date 2022/9/23
 */
@Service
@RequiredArgsConstructor
public class SewageIndexService {
    private SewageIndexMapper sewageIndexMapper;
    private final JdbcTemplate locationJdbcTemplate;

    private DomesticSewageMapper domesticSewageMapper;

    private DomesticSewageOdsMapper domesticSewageOdsMapper;

    private DomesticSewageAmmoniaNitrogenMapper domesticSewageAmmoniaNitrogenMapper;

    private IndustrialWastewaterMapper industrialWastewaterMapper;

    private IndustrialWastewaterOdsMapper industrialWastewaterOdsMapper;

    private IndustrialWastewaterAmmoniaNitrogenMapper industrialWastewaterAmmoniaNitrogenMapper;

    @Autowired
    public void setIndustrialWastewaterAmmoniaNitrogenMapper(IndustrialWastewaterAmmoniaNitrogenMapper industrialWastewaterAmmoniaNitrogenMapper) {
        this.industrialWastewaterAmmoniaNitrogenMapper = industrialWastewaterAmmoniaNitrogenMapper;
    }

    @Autowired
    public void setIndustrialWastewaterOdsMapper(IndustrialWastewaterOdsMapper industrialWastewaterOdsMapper) {
        this.industrialWastewaterOdsMapper = industrialWastewaterOdsMapper;
    }

    @Autowired
    public void setIndustrialWastewaterMapper(IndustrialWastewaterMapper industrialWastewaterMapper) {
        this.industrialWastewaterMapper = industrialWastewaterMapper;
    }

    @Autowired
    public void setDomesticSewageMapper(DomesticSewageMapper domesticSewageMapper) {
        this.domesticSewageMapper = domesticSewageMapper;
    }

    @Autowired
    public void setDomesticSewageOdsMapper(DomesticSewageOdsMapper domesticSewageOdsMapper) {
        this.domesticSewageOdsMapper = domesticSewageOdsMapper;
    }

    @Autowired
    public void setDomesticSewageAmmoniaNitrogenMapper(DomesticSewageAmmoniaNitrogenMapper domesticSewageAmmoniaNitrogenMapper) {
        this.domesticSewageAmmoniaNitrogenMapper = domesticSewageAmmoniaNitrogenMapper;
    }

    @Autowired
    public void setSewageIndexMapper(SewageIndexMapper sewageIndexMapper) {
        this.sewageIndexMapper = sewageIndexMapper;
    }

    public SewageIndexEntity add(SewageIndexEntity sewageIndexEntity) {
        String sewageType = sewageIndexEntity.getSewageType();

        String mineCode = sewageIndexEntity.getMineCode();
        if (StringUtils.isEmpty(mineCode)){
            LoginUser loginUser = LoginHelper.getLoginUser();
            mineCode = loginUser.getOrganizationCode();
        }
        sewageIndexEntity.setMineCode(mineCode);

        SewageIndexEntity test = sewageIndexMapper.findByFillInTimeAndSewageType(sewageIndexEntity.getFillInTime(), sewageType);
        //添加，且以存在指定填报时间
        if (test != null && sewageIndexEntity.getId() == null) {
            return null;
        }
        if ("1".equals(sewageType)) {
            sewageIndexEntity.setDescribe("华能生活污水处理站运行记录填报");
        } else if ("2".equals(sewageType)) {
            sewageIndexEntity.setDescribe("华能工业污水处理站运行记录填报");
        }
        return sewageIndexMapper.save(sewageIndexEntity);
    }

    public SewageIndexEntity findByid(Long id) {
        return sewageIndexMapper.getById(id);
    }

    //工业污水值班人员更新
    public void updateIndustriaWastewater(IndustrialWastewaterShiftEntity industrialWastewaterShiftEntity) {
        SewageIndexEntity sewageIndexEntity = sewageIndexMapper.findById(industrialWastewaterShiftEntity.getIndexId()).get();
        //获取班次
        String shift = industrialWastewaterShiftEntity.getShift();
        String staffOnDuty = industrialWastewaterShiftEntity.getPersonOnDuty();
        String staffOnDutyCode = industrialWastewaterShiftEntity.getPersonOnDutyCode();
        String remake = industrialWastewaterShiftEntity.getRemark();

        sewageIndexMapper.save(updateStaffOnDuty(sewageIndexEntity, shift, staffOnDuty, staffOnDutyCode,remake,"indust"));
    }

    //生活污水值班人员更新
    public void updateDomesticSewage(DomesticSewageShiftEntity domesticSewageShiftEntity) {
        SewageIndexEntity sewageIndexEntity = sewageIndexMapper.findById(domesticSewageShiftEntity.getIndexId()).get();
        //获取班次
        String shift = domesticSewageShiftEntity.getShift();
        String staffOnDuty = domesticSewageShiftEntity.getPersonOnDuty();
        String staffOnDutyCode = domesticSewageShiftEntity.getPersonOnDutyCode();

        //班次异常情况
        boolean judgmentStatus = domesticSewageJudgmentStatus(domesticSewageShiftEntity);
        if (!judgmentStatus) {
            updateState(sewageIndexEntity, shift);
        }
        String remark = domesticSewageShiftEntity.getRemark();
        sewageIndexMapper.save(updateStaffOnDuty(sewageIndexEntity, shift, staffOnDuty, staffOnDutyCode,remark,"domest"));
    }

    private void updateState(SewageIndexEntity sewageIndexEntity, String shift) {
        String state = sewageIndexEntity.getState();
        if ("1".equals(shift)) {
            shift = "早班";
        }
        if ("2".equals(shift)) {
            shift = "中班";
        }
        if ("3".equals(shift)) {
            shift = "晚班";
        }
        if (state == null || "".equals(state)) {
            state = shift + ";";
            sewageIndexEntity.setState(state);
        } else {
            String[] strings = state.split(";");
            for (String s : strings) {
                if (shift.equals(s)) {
                    return;
                }
            }
            state += shift + ";";
            sewageIndexEntity.setState(state);
        }
    }

    private SewageIndexEntity updateStaffOnDuty(SewageIndexEntity sewageIndexEntity, String shift, String newStaffOnDuty, String newStaffOnDutyCode,String remake,String type) {
        //获取值班人姓名
        Map<String, String> shiftMap = new HashMap<>();
        Map<String, String> remarkMap = new HashMap<>();
        if ("".equals(newStaffOnDuty)) {
            newStaffOnDuty = "未设置";
        }
        if ("domest".equals(type)){
            List<DomesticSewageShiftEntity> domList = domList(sewageIndexEntity.getId());
            for (DomesticSewageShiftEntity dom:domList){
                shiftMap.put(dom.getShift(),dom.getPersonOnDuty());
                remarkMap.put(dom.getShift(),dom.getRemark());
            }
        }
        if ("indust".equals(type)){
            List<IndustrialWastewaterShiftEntity> industList = indList(sewageIndexEntity.getId());
            for (IndustrialWastewaterShiftEntity indust:industList){
                shiftMap.put(indust.getShift(),indust.getPersonOnDuty());
                remarkMap.put(indust.getShift(),indust.getRemark());
            }
        }
        String staffOnDuty = "";
        String remakeOnDuty = "";

        //更新数据
        shiftMap.put(shift.trim(), newStaffOnDuty);
        shiftMap.put(shift.trim() + "code", newStaffOnDutyCode);
        remarkMap.put(shift.trim(),remake);

        //数据合并到主表
        staffOnDuty = stringMerge(staffOnDuty,shiftMap);
        remakeOnDuty = stringMerge(remakeOnDuty,remarkMap);

        sewageIndexEntity.setStaffOnDuty(staffOnDuty);
        sewageIndexEntity.setRemark(remakeOnDuty);

        return sewageIndexEntity;
    }

    private void stringSegmentation(String string,Map<String, String> stringMap){
        if (string!=null){
            String[] strings = string.split(";");
            //备注
            for (String s : strings) {
                String beginning = s.split(":")[0];
                if ("早班".equals(beginning)) {
                    stringMap.put("1", s.split(":")[1]);
                }
                if ("中班".equals(beginning)) {
                    stringMap.put("2", s.split(":")[1]);
                }
                if ("晚班".equals(beginning)) {
                    stringMap.put("3", s.split(":")[1]);
                }
            }
        }
    }

    private String stringMerge(String string,Map<String, String> stringMap){
        string = "";
        if (stringMap.get("1") != null) {
            string += "早班:" + stringMap.get("1") + ";";
        }
        if (stringMap.get("2") != null) {
            string += "中班:" + stringMap.get("2") + ";";
        }
        if (stringMap.get("3") != null) {
            string += "晚班:" + stringMap.get("3") + ";";
        }
        return string;
    }

    //生活污水各系统状态
    public Boolean domesticSewageJudgmentStatus(DomesticSewageShiftEntity domesticSewageShiftEntity) {

        String mudPress = domesticSewageShiftEntity.getMudPress();
        String mudScraper = domesticSewageShiftEntity.getMudScraper();
        String liftPump = domesticSewageShiftEntity.getLiftPump();
        String sludgePump = domesticSewageShiftEntity.getSludgePump();
        String stirringPump = domesticSewageShiftEntity.getStirringPump();
        String backwashPump = domesticSewageShiftEntity.getBackwashPump();
        String rootsBlower = domesticSewageShiftEntity.getRootsBlower();

        if (!"0".equals(mudPress)) {
            return false;
        }
        if (!"0".equals(mudScraper)) {
            return false;
        }
        if (!"0".equals(liftPump)) {
            return false;
        }
        if (!"0".equals(sludgePump)) {
            return false;
        }
        if (!"0".equals(stirringPump)) {
            return false;
        }
        if (!"0".equals(backwashPump)) {
            return false;
        }
        if (!"0".equals(rootsBlower)) {
            return false;
        }
        return true;
    }

    public ResponsePage<Map<String, Object>> domPageList(PageQuery pageQuery, SewageIndexParameter sewageIndexParameter) {
        StringBuffer sql = new StringBuffer("SELECT\n" +
                "\t* \n" +
                "FROM\n" +
                "\tsewage_index \n" +
                "WHERE 1=1");
        StringBuffer countSql = new StringBuffer("SELECT\n" +
                "\tcount(*) \n" +
                "FROM\n" +
                "\tsewage_index \n" +
                "WHERE 1=1");
        StringBuffer where = new StringBuffer("");
        String beginTime = sewageIndexParameter.getBeginTime();
        String endTime = sewageIndexParameter.getEndTime();
        String people = sewageIndexParameter.getPeople();
        String parameter = sewageIndexParameter.getParameter();
        String sewageTypes = sewageIndexParameter.getSewageType();
        String mineCode = sewageIndexParameter.getMineCode();

        if (mineCode!=null&&"".equals(mineCode)){
            where.append(" and mine_code = '"+mineCode+"'");
        }

        if (sewageTypes != null && !"".equals(sewageTypes)) {
            where.append(" and sewage_type = '" + sewageTypes + "'");
        }
        if (beginTime != null || endTime != null) {
            if (beginTime != null && !"".equals(beginTime)) {
                where.append("and fill_in_time >= '" + beginTime + "'");
            }
            if (endTime != null && !"".equals(endTime)) {
                where.append("and fill_in_time <= '" + endTime + "'");
            }
        }
        if (people != null && !"".equals(people)) {
            String[] peoples = people.split(",");
            where.append("and ( 1=0 ");
            for (String p : peoples) {
                where.append("or staff_on_duty_code LIKE'%" + p + "%'");
            }
            where.append(") ");
        }
        if (parameter != null && !"".equals(parameter)) {
            String[] parameters = parameter.split(",");
            where.append("and ( 1=0 ");
            for (String p : parameters) {
                if (!"0".equals(p)) {
                    if ("1".equals(p)) {
                        p = "早班";
                    }
                    if ("2".equals(p)) {
                        p = "中班";
                    }
                    if ("3".equals(p)) {
                        p = "晚班";
                    }
                    where.append("or \"state\" LIKE'%" + p + "%'");
                } else {
                    where.append("or (\"state\" = '' or \"state\" is null)");
                }
            }
            where.append(") ");
        }
        sql.append(where);
        Integer pageSize = pageQuery.getPageSize();
        Integer pageNum = pageQuery.getPageNum();
        if (pageSize == null) {
            pageSize = 10;
        }
        if (pageNum == null || pageNum == 0) {
            pageNum = 1;
        }
        sql.append(" order by fill_in_time desc");
        sql.append(" LIMIT " + (pageSize + 1) + " OFFSET " + (pageNum - 1) * pageSize);

        countSql.append(where);
        List<Map<String, Object>> lists = locationJdbcTemplate.queryForList(countSql.toString());
        List<Map<String, Object>> list = locationJdbcTemplate.queryForList(sql.toString());
        for (Map<String, Object> map : list) {
            String sewageType = map.get("sewage_type") + "";
            if ("1".equals(sewageType)) {
                List<DomesticSewageShiftEntity> shifts = domList(Long.parseLong(map.get("id") + ""));
                StringBuffer staff_on_duty=new StringBuffer();
                for (DomesticSewageShiftEntity entity:shifts){
                    if(StringUtils.isNotBlank(entity.getPersonOnDuty())) {
                        if ("1".equals(entity.getShift().trim())) {
                            staff_on_duty.append("早班：").append(entity.getPersonOnDuty()).append(";");
                        }else if("2".equals(entity.getShift().trim())){
                            staff_on_duty.append("中班：").append(entity.getPersonOnDuty()).append(";");
                        }else if ("3".equals(entity.getShift().trim())){
                            staff_on_duty.append("晚班：").append(entity.getPersonOnDuty()).append(";");
                        }
                    }
                }
                map.put("staff_on_duty",staff_on_duty.toString());
                //生活污水列表
                map.put("shift", shifts);
            } else if ("2".equals(sewageType)) {
                List<IndustrialWastewaterShiftEntity> shifts = indList(Long.parseLong(map.get("id") + ""));
                StringBuffer staff_on_duty=new StringBuffer();
                for (IndustrialWastewaterShiftEntity entity:shifts){
                    if(StringUtils.isNotBlank(entity.getPersonOnDuty())) {
                        if ("1".equals(entity.getShift().trim())) {
                            staff_on_duty.append("早班：").append(entity.getPersonOnDuty()).append(";");
                        }else if("2".equals(entity.getShift().trim())){
                            staff_on_duty.append("中班：").append(entity.getPersonOnDuty()).append(";");
                        }else if ("3".equals(entity.getShift().trim())){
                            staff_on_duty.append("晚班：").append(entity.getPersonOnDuty()).append(";");
                        }
                    }
                }
                map.put("staff_on_duty",staff_on_duty.toString());
                //工业污水列表
                map.put("shift", shifts);
            }
        }
        if ("1".equals(sewageIndexParameter.getSewageType())) {
            list = domLast(pageSize, list);
        } else if ("2".equals(sewageIndexParameter.getSewageType())) {
            list = indLast(pageSize, list);
        }

        return new ResponsePage<Map<String, Object>>(list, Integer.parseInt(lists.get(0).get("count") + ""));
    }

    //处理生活污水上一班次污水处理量方法
    private List<Map<String, Object>> indLast(Integer pageSize, List<Map<String, Object>> list) {
        List<Map<String, Object>> lists = new ArrayList<>();
        String SewageTreatment = "0";
        String Overflow = "0";
        String Reuse = "0";
        String CoolingSprinkler = "0";
        String Downhole = "0";
        String RaisedPool = "0";
        String GroutingStation = "0";
        for (int i = 0; i < list.size(); i++) {
            Map<String, Object> map = list.get(list.size() - i - 1);
            List<IndustrialWastewaterShiftEntity> industrialWastewaterShiftEntities = (List<IndustrialWastewaterShiftEntity>) map.get("shift");
            if (industrialWastewaterShiftEntities == null) {
                industrialWastewaterShiftEntities = new ArrayList<>();
            }
            for (IndustrialWastewaterShiftEntity in : industrialWastewaterShiftEntities) {
                if ("1".equals(in.getShift())) {
                    in.setLastSewageTreatment(SewageTreatment);
                    in.setLastOverflow(Overflow);
                    in.setLastReuse(Reuse);
                    in.setLastCoolingSprinkler(CoolingSprinkler);
                    in.setLastDownhole(Downhole);
                    in.setLastRaisedPool(RaisedPool);
                    in.setLastGroutingStation(GroutingStation);
                    SewageTreatment = in.getSewageTreatment();
                    Overflow = in.getOverflow();
                    Reuse = in.getReuse();
                    CoolingSprinkler = in.getCoolingSprinkler();
                    Downhole = in.getDownhole();
                    RaisedPool = in.getRaisedPool();
                    GroutingStation = in.getGroutingStation();
                    break;
                }
            }
            for (IndustrialWastewaterShiftEntity in : industrialWastewaterShiftEntities) {
                if ("2".equals(in.getShift())) {
                    in.setLastSewageTreatment(SewageTreatment);
                    in.setLastOverflow(Overflow);
                    in.setLastReuse(Reuse);
                    in.setLastCoolingSprinkler(CoolingSprinkler);
                    in.setLastDownhole(Downhole);
                    in.setLastRaisedPool(RaisedPool);
                    in.setLastGroutingStation(GroutingStation);
                    SewageTreatment = in.getSewageTreatment();
                    Overflow = in.getOverflow();
                    Reuse = in.getReuse();
                    CoolingSprinkler = in.getCoolingSprinkler();
                    Downhole = in.getDownhole();
                    RaisedPool = in.getRaisedPool();
                    GroutingStation = in.getGroutingStation();
                    break;
                }
            }
            for (IndustrialWastewaterShiftEntity in : industrialWastewaterShiftEntities) {
                if ("3".equals(in.getShift())) {
                    in.setLastSewageTreatment(SewageTreatment);
                    in.setLastOverflow(Overflow);
                    in.setLastReuse(Reuse);
                    in.setLastCoolingSprinkler(CoolingSprinkler);
                    in.setLastDownhole(Downhole);
                    in.setLastRaisedPool(RaisedPool);
                    in.setLastGroutingStation(GroutingStation);
                    SewageTreatment = in.getSewageTreatment();
                    Overflow = in.getOverflow();
                    Reuse = in.getReuse();
                    CoolingSprinkler = in.getCoolingSprinkler();
                    Downhole = in.getDownhole();
                    RaisedPool = in.getRaisedPool();
                    GroutingStation = in.getGroutingStation();
                    break;
                }
            }
            if (list.size() > pageSize) {
                if (i != 0) {
                    lists.add(0, map);
                }
            } else {
                lists.add(0, map);
            }
        }

        return lists;
    }

    //处理生活污水上一班次污水处理量方法
    private List<Map<String, Object>> domLast(Integer pageSize, List<Map<String, Object>> list) {
        Long SewageTreatment = 0l;
        Long Overflow = 0l;
        Long Reuse = 0l;
        List<Map<String, Object>> lists = new ArrayList<>();
        for (int i = 0; i < list.size(); i++) {
            Map<String, Object> map = list.get(list.size() - i - 1);
            List<DomesticSewageShiftEntity> domesticSewageShiftEntities = (List<DomesticSewageShiftEntity>) map.get("shift");
            if (domesticSewageShiftEntities == null) {
                domesticSewageShiftEntities = new ArrayList<>();
            }
            for (DomesticSewageShiftEntity dom : domesticSewageShiftEntities) {
                if ("1".equals(dom.getShift())) {
                    dom.setLastSewageTreatment(SewageTreatment + "");
                    dom.setLastOverflow(Overflow + "");
                    dom.setLastReuse(Reuse + "");
                    SewageTreatment = Long.parseLong(dom.getSewageTreatment());
                    Overflow = Long.parseLong(dom.getSewageTreatment());
                    Reuse = Long.parseLong(dom.getSewageTreatment());
                    break;
                }
            }
            for (DomesticSewageShiftEntity dom : domesticSewageShiftEntities) {
                if ("2".equals(dom.getShift())) {
                    dom.setLastSewageTreatment(SewageTreatment + "");
                    dom.setLastOverflow(Overflow + "");
                    dom.setLastReuse(Reuse + "");
                    SewageTreatment = Long.parseLong(dom.getSewageTreatment());
                    Overflow = Long.parseLong(dom.getSewageTreatment());
                    Reuse = Long.parseLong(dom.getSewageTreatment());
                    break;
                }
            }
            for (DomesticSewageShiftEntity dom : domesticSewageShiftEntities) {
                if ("3".equals(dom.getShift())) {
                    dom.setLastSewageTreatment(SewageTreatment + "");
                    dom.setLastOverflow(Overflow + "");
                    dom.setLastReuse(Reuse + "");
                    SewageTreatment = Long.parseLong(dom.getSewageTreatment());
                    Overflow = Long.parseLong(dom.getSewageTreatment());
                    Reuse = Long.parseLong(dom.getSewageTreatment());
                    break;
                }
            }

            if (list.size() > pageSize) {
                if (i != 0) {
                    lists.add(0, map);
                }
            } else {
                lists.add(0, map);
            }
        }
        return lists;

    }


    public List<DomesticSewageShiftEntity> domList(Long indexId) {
        List<DomesticSewageShiftEntity> list = domesticSewageMapper.findAllByIndexIdOrderByShiftAsc(indexId);
        for (DomesticSewageShiftEntity domesticSewageShiftEntity : list) {
            domesticSewageShiftEntity.setDomesticSewageOdsEntities(domesticSewageOdsMapper.findAllByShiftId(domesticSewageShiftEntity.getId()));
            domesticSewageShiftEntity.setDomesticSewageAmmoniaNitrogenEntities(domesticSewageAmmoniaNitrogenMapper.findAllByShiftId(domesticSewageShiftEntity.getId()));
        }
        return list;
    }

    public List<IndustrialWastewaterShiftEntity> indList(Long indexId) {
        List<IndustrialWastewaterShiftEntity> list = industrialWastewaterMapper.findAllByIndexIdOrderByShiftAsc(indexId);
        for (IndustrialWastewaterShiftEntity industrialWastewaterShiftEntity : list) {
            industrialWastewaterShiftEntity.setIndustrialWastewaterOdsEntityList(industrialWastewaterOdsMapper.findAllByShiftId(industrialWastewaterShiftEntity.getId()));
            industrialWastewaterShiftEntity.setIndustrialWastewaterAmmoniaNitrogenEntityList(industrialWastewaterAmmoniaNitrogenMapper.findAllByShiftId(industrialWastewaterShiftEntity.getId()));
        }
        return list;
    }

    public void delete(Long id) {
        sewageIndexMapper.deleteById(id);
    }

    public CalculationVo calculation(String date, Integer shit, String sewageType) throws ParseException {
        Integer shit2;
        String date2=date;
        if(shit==1){
            SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
            Date parse = sdf.parse(date);
            Calendar calendar=Calendar.getInstance();
            calendar.setTime(parse);
            calendar.set(Calendar.DAY_OF_MONTH,calendar.get(Calendar.DAY_OF_MONTH)-1);
            date2=sdf.format(calendar.getTime());
            shit2=3;
        }else {
            shit2=shit-1;
        }
        if ("1".equals(sewageType)) {
            String sql ="SELECT\n" +
                    "	* \n" +
                    "FROM\n" +
                    "	(\n" +
                    "	SELECT SUM\n" +
                    "		( to_number( t1.sewage_treatment, '99999999.99' ) ) AS sewageTotal,\n" +
                    "		SUM ( to_number( t1.reuse, '99999999.99' ) ) AS reuseTotal \n" +
                    "	FROM\n" +
                    "		domestic_sewage_shift t1\n" +
                    "		JOIN sewage_index t2 ON ( t1.index_id = t2.\"id\" and  t2.sewage_type='1' ) \n" +
                    "	WHERE\n" +
                    "		t2.fill_in_time < to_date(?, 'yyyy-MM-dd') \n" +
                    "		OR ( t2.fill_in_time = to_date(?, 'yyyy-MM-dd') AND t1.shift <= ? ) \n" +
                    "	) t3 FULL JOIN \n" +
                    "	(\n" +
                    "	SELECT\n" +
                    "		SUM(to_number( t1.sewage_treatment, '99999999.99' )) AS lastSewage,\n" +
                    "		SUM(to_number( t1.reuse, '99999999.99' )) AS lastReuse \n" +
                    "	FROM\n" +
                    "		domestic_sewage_shift t1\n" +
                    "		JOIN sewage_index t2 ON ( t1.index_id = t2.\"id\" and  t2.sewage_type='1' ) \n" +
                    "	WHERE\n" +
                    "		t2.fill_in_time < to_date(?, 'yyyy-MM-dd') \n" +
                    "		OR ( t2.fill_in_time = to_date(?, 'yyyy-MM-dd') AND t1.shift <= ? ) \n" +
                    "	) t4 on 1=1 ";
            return locationJdbcTemplate.queryForObject(sql, new Object[]{date, date, shit.toString(),date2,date2,shit2.toString()}, new BeanPropertyRowMapper<CalculationVo>(CalculationVo.class));
        }else{
            String sql="SELECT\n" +
                    "	* \n" +
                    "FROM\n" +
                    "	(\n" +
                    "	SELECT SUM\n" +
                    "		( to_number( t1.sewage_treatment, '99999999.99' ) ) AS sewageTotal,\n" +
                    "		SUM ( to_number( t1.overflow, '99999999.99' ) ) AS overflowTotal,\n" +
                    "		SUM ( to_number( t1.cooling_sprinkler, '99999999.99' ) ) AS coolingTotal,\n" +
                    "		SUM ( to_number( t1.downhole, '99999999.99' ) ) AS downholeTotal,\n" +
                    "		SUM ( to_number( t1.raised_pool, '99999999.99' ) ) AS poolTotal,\n" +
                    "		SUM ( to_number( t1.grouting_station, '99999999.99' ) ) AS groutingTotal\n" +
                    "	FROM\n" +
                    "		industrial_wastewater_shift t1\n" +
                    "		JOIN sewage_index t2 ON ( t1.index_id = t2.\"id\" and  t2.sewage_type='2') \n" +
                    "	WHERE\n" +
                    "		t2.fill_in_time < to_date('%s', 'yyyy-MM-dd')  \n" +
                    "		OR ( t2.fill_in_time = to_date('%s', 'yyyy-MM-dd')  AND t1.shift <= '%s' ) \n" +
                    "	) t3 FULL JOIN\n" +
                    "	(\n" +
                    "	SELECT\n" +
                    "		SUM(to_number( t1.sewage_treatment, '99999999.99' )) AS lastSewage,\n" +
                    "		SUM(to_number( t1.overflow, '99999999.99' )) as lastOverflow,\n" +
                    "		SUM(to_number( t1.cooling_sprinkler, '99999999.99' )) as lastCooling,\n" +
                    "		SUM(to_number( t1.downhole, '99999999.99' )) as lastDownhole,\n" +
                    "		SUM(to_number( t1.raised_pool, '99999999.99' )) as lastPool,\n" +
                    "		SUM(to_number( t1.grouting_station, '99999999.99' )) as lastGrouting\n" +
                    "	FROM\n" +
                    "		industrial_wastewater_shift t1\n" +
                    "		JOIN sewage_index t2 ON ( t1.index_id = t2.\"id\" and  t2.sewage_type='2' ) \n" +
                    "	WHERE\n" +
                    "		t2.fill_in_time < to_date('%s', 'yyyy-MM-dd')  \n" +
                    "		OR ( t2.fill_in_time = to_date('%s', 'yyyy-MM-dd')  AND t1.shift <= '%s' ) \n" +
                    "	) t4 on 1=1\n" ;
            String format = String.format(sql, new Object[]{date, date, shit.toString(), date2,date2, shit2.toString()});
            return locationJdbcTemplate.queryForObject( format, new BeanPropertyRowMapper<CalculationVo>(CalculationVo.class));
        }
    }
}
